﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using SCRUM.Core.DAO;
using System.Collections.Specialized;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database for comments
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Returns all threads for specific entity
        /// </summary>
        /// <param name="entityID">ID of entity</param>
        /// <param name="type">Type of entity, task, bli, userstory, ...</param>
        /// <returns>List of threads</returns>
        public override List<Thread> GetAllThreadsForEntity(int entityID, string type)
        {
            List<Thread> result = new List<Thread>();
            try
            {
                DbParameter dbEntityID = CreateParameter("entityID", entityID);
                DbParameter dbType = CreateParameter("type", type);
                connection.Open();
                DbDataReader data = GetGrid("threads/GetAllThreadsForEntity", dbEntityID, dbType);
                while (data.Read())
                {
                    Thread thread = new Thread(GetIntByColumn(data, "ID"),
                                                GetStringByColumn(data, "THREAD_NAME"),
                                                type,
                                                entityID);
                    result.Add(thread);
                }
            }
            finally
            {
                connection.Close();
            }

            return result;
        }

        /// <summary>
        /// Gets Thread object asociated with specified ID
        /// </summary>
        /// <param name="ID">ID of thread</param>
        /// <returns>Thread object</returns>
        public override Thread GetThreadByID(int ID)
        {
            Thread result;
            DbParameter dbthreadID = CreateParameter("threadID", ID);

            try
            {
                connection.Open();
                DbDataReader data = GetVector("threads/GetThreadByID", dbthreadID);
                if (!data.Read())
                {
                    throw new Exception("Thread NOT found!");
                }
                else
                {
                    result = new Thread(ID,
                                        GetStringByColumn(data, "THREAD_NAME"),
                                        GetStringByColumn(data, "TYPE"),
                                        GetIntByColumn(data, "ENTITY_ID"));
                }
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Gets Comment object asociated with specified ID
        /// </summary>
        /// <param name="ID">ID of Comment</param>
        /// <returns>Comment object</returns>
        public override Comment GetCommentByID(int ID)
        {
            Comment result;
            DbParameter dbCommentID = CreateParameter("ID", ID);

            try
            {
                connection.Open();
                DbDataReader data = GetVector("threads/GetCommentByID", dbCommentID);
                if (!data.Read())
                {
                    throw new Exception("Comment NOT found!");
                }
                else
                {
                    Thread thr = new Thread();
                    thr.ID = GetIntByColumn(data, "THREAD_ID");
                    result = new Comment(ID,
                                                  GetStringByColumn(data,"COMMENT"),
                                                  GetDateByColumn(data,"DATE"),
                                                  GetStringByColumn(data, "USER_LOGIN"),
                                                  thr);
                }
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Gets all comments for specific thread
        /// </summary>
        /// <param name="threadID">ID of thread</param>
        /// <returns>List of comments for specified thread</returns>
        public override List<Comment> GetAllCommentsForThread(int threadID)
        {
            List<Comment> result = new List<Comment>();
            try
            {
                DbParameter dbThreadID = CreateParameter("threadID", threadID);
                connection.Open();
                DbDataReader data = GetGrid("threads/GetAllCommentsForThread", dbThreadID);
                while (data.Read())
                {
                    Thread thr = new Thread();
                    thr.ID = threadID;
                    Comment comment = new Comment(GetIntByColumn(data, "ID"),
                                                  GetStringByColumn(data,"COMMENT"),
                                                  GetDateByColumn(data,"DATE"),
                                                  GetStringByColumn(data, "USER_LOGIN"),
                                                  thr);
                    result.Add(comment);
                }
            }
            finally
            {
                connection.Close();
            }

            return result;        
        }

        /// <summary>
        /// Returns all subcomments for comment
        /// </summary>
        /// <param name="ID">Id of comment</param>
        /// <returns>List of sub comments</returns>
        public override List<Comment> GetSubCommentsForComment(int ID)
        {
            List<Comment> result = new List<Comment>();
            try
            {
                DbParameter dbCommentID = CreateParameter("commentID", ID);
                connection.Open();
                DbDataReader data = GetGrid("threads/GetSubCommentsForComment", dbCommentID);
                while (data.Read())
                {
                    Thread thr = new Thread();
                    thr.ID = GetIntByColumn(data, "THREAD_ID");
                    Comment comment = new Comment(GetIntByColumn(data,"ID"),
                                                  GetStringByColumn(data,"COMMENT"),
                                                  GetDateByColumn(data,"DATE"),
                                                  GetStringByColumn(data, "USER_LOGIN"),
                                                  thr);
                    result.Add(comment);
                }
            }
            finally
            {
                connection.Close();
            }

            return result;        
        }

        /// <summary>
        /// Creates new thread for entity
        /// </summary>
        /// <param name="thread">Thread to insert</param>
        /// <returns>Created thread with id</returns>
        public override Thread CreateNewThread(Thread thread)
        {
            DbParameter dbName = CreateParameter("name", thread.Name);
            DbParameter dbType = CreateParameter("type", thread.TypeOfEntity);
            DbParameter dbEntityID = CreateParameter("entityID", thread.EntityID);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("threads/InsertThread", dbName, dbType, dbEntityID);
                if (data != 1)
                {
                    throw new Exception("Thread NOT created!");
                }
                thread.ID = Convert.ToInt32(GetScalar("threads/GetLastInsertedThreadID"));
            }
            finally
            {
                connection.Close();
            }

            return thread;
        }

        /// <summary>
        /// Creates new comment for entity
        /// </summary>
        /// <param name="comment">Comment to insert</param>
        /// <param name="inComment">Comment that this comment belongs to</param>
        /// <returns>Created comment with id</returns>
        public override Comment CreateNewComment(Comment comment, int inComment)
        {
            DbParameter dbText = CreateParameter("comment", comment.Text);
            DbParameter dbThreadID = CreateParameter("threadID", comment.InThread.ID);
            
            DbParameter dbUserID = CreateParameter("userID", comment.User.Login);
            DbParameter dbDate = CreateParameter("date", comment.Date);
            try
            {
                connection.Open();
                int data = 0;
                if (inComment != -1)
                {
                    DbParameter dbCommentID = CreateParameter("commentID", inComment);
                    data = ExecuteNonQuery("threads/InsertComment", dbText, dbThreadID, dbCommentID, dbUserID, dbDate);
                }
                else
                {
                    data = ExecuteNonQuery("threads/InsertComment2", dbText, dbThreadID, dbUserID, dbDate);
                }
                if (data != 1)
                {
                    throw new Exception("Comment NOT created!");
                }
                comment.ID = Convert.ToInt32(GetScalar("threads/GetLastInsertedCommentID"));
            }
            finally
            {
                connection.Close();
            }

            return comment;
        }

        /// <summary>
        /// Updates thread
        /// </summary>
        /// <param name="thread">Thread to update</param>
        public override void UpdateThread(Thread thread)
        {
            DbParameter dbID = CreateParameter("ID", thread.ID);
            DbParameter dbName = CreateParameter("name", thread.Name);
            DbParameter dbType = CreateParameter("type", thread.TypeOfEntity);
            DbParameter dbEntityID = CreateParameter("entityID", thread.EntityID);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("threads/UpdateThread",dbID, dbName, dbType, dbEntityID);
                if (data != 1)
                {
                    throw new Exception("Thread NOT updated!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes thread
        /// </summary>
        /// <param name="threadID">ID of Thread to delete</param>
        public override void DeleteThread(int threadID)
        {
            DbParameter dbID = CreateParameter("ID", threadID);

            try
            {
                connection.Open();
                int data = ExecuteNonQuery("threads/DeleteThread", dbID);
                if (data != 1)
                {
                    throw new Exception("Thread NOT deleted!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates comment
        /// </summary>
        /// <param name="comment">Comment to update</param>
        /// <param name="inComment">Comment that this comment reacts on</param>
        public override void UpdateComment(Comment comment, int inComment)
        {
            DbParameter dbID = CreateParameter("ID", comment.ID);
            DbParameter dbText = CreateParameter("comment", comment.Text);
            DbParameter dbThreadID = CreateParameter("threadID", comment.InThread);
            DbParameter dbCommentID = CreateParameter("commentID", inComment);
            DbParameter dbUserID = CreateParameter("userID", comment.User.Login);
            DbParameter dbDate = CreateParameter("date", comment.Date);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("threads/UpdateComment", dbID, dbText, dbThreadID, dbCommentID, dbUserID, dbDate);
                if (data != 1)
                {
                    throw new Exception("Comment NOT updated!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes comment
        /// </summary>
        /// <param name="commentID">ID of Comment to delete</param>
        public override void DeleteComment(int commentID)
        {
            DbParameter dbID = CreateParameter("ID", commentID);

            try
            {
                connection.Open();
                int data = ExecuteNonQuery("threads/DeleteComment", dbID);
                if (data != 1)
                {
                    throw new Exception("Comment NOT deleted!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets all Comments object asociated with specified ID of entity
        /// </summary>
        /// <param name="ID">ID of Entity</param>
        /// <param name="type">Type of entity</param>
        /// <returns>All Comments </returns>
        public override IList<Comment> GetCommentsForEntity(int ID, string type)
        {
            IList<Comment> result = new List<Comment>();
            try
            {
                DbParameter dbEntityID = CreateParameter("ID", ID);
                DbParameter dbtypeID = CreateParameter("type", type);
                connection.Open();
                DbDataReader data = GetGrid("threads/GetCommentsForEntity", dbEntityID, dbtypeID);
                while (data.Read())
                {
                    Thread thr = new Thread();
                    thr.ID = GetIntByColumn(data, "THREAD_ID");
                    Comment comment = new Comment(GetIntByColumn(data, "ID"),
                                                  GetStringByColumn(data, "COMMENT"),
                                                  GetDateByColumn(data, "DATE"),
                                                  GetStringByColumn(data, "USER_LOGIN"),
                                                  thr);
                    result.Add(comment);
                }
            }
            finally
            {
                connection.Close();
            }

            return result;
        }
    }
}
